using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;

namespace XBLib.Dal.AdoDal
{
    public class LocalException : Exception
    {
        public LocalException(string message, Exception inner)
            : base(message, inner)
        { }
    }

    public sealed class AdoDaoHelper
    {
        private static readonly AdoDaoHelper _instance = new AdoDaoHelper();
        private static readonly string CONNECTION_STRING_APP_SETTING = "connectDB";
        private DbTransaction m_transaction;
        private DbConnection m_Connect;

        public DbConnection Connect
        {
            get { return m_Connect; }
            set { m_Connect = value; }
        }
     public DbTransaction Transaction {
       get { return m_transaction; }
       set { m_transaction = value; }
   }

 

        private AdoDaoHelper()
        { }

        public static AdoDaoHelper Instance()
        {
            return _instance;
        }

        private ConnectionStringSettings getConnectionString()
        {
            string connectionStringName = string.Empty;
            ConnectionStringSettings connectionString = null;
           // connectionStringName = ConfigurationManager.AppSettings[CONNECTION_STRING_APP_SETTING];
            connectionStringName = "connectDB";
            connectionString = ConfigurationManager.ConnectionStrings[connectionStringName];
            return connectionString;
        }

        public DbConnection GetConnection()
        {
            ConnectionStringSettings connectionString = getConnectionString();
            DbConnection connection = null;
            if (connectionString != null)
            {
                try
                {
                    DbProviderFactory factory = DbProviderFactories.GetFactory(connectionString.ProviderName);
                    connection = factory.CreateConnection();
                    connection.ConnectionString = connectionString.ConnectionString;
                }
                catch (Exception ex)
                {
                    if (connection != null)
                    {
                        connection = null;
                    }
                    throw ex;
                }
            }
            return connection;
        }

        public DbCommand CreateCommand(string commandText)
        {
            if (m_Connect == null)
            {
                m_Connect = GetConnection();
            }
            DbConnection connection = m_Connect;
            DbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.Transaction = m_transaction;
            return command;
        }

        public DbCommand CreateCommand(string commandText, CommandType type)
        {
            DbCommand command = CreateCommand(commandText);
            command.CommandType = type;
            return command;
        }

        public DbCommand CreateCommand(string commandText, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(commandText);
            foreach (DbParameter parameter in parameters)
                command.Parameters.Add(parameter);
            return command;
        }

        public DbCommand CreateCommand(string commandText, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(commandText);
            command.Parameters.AddRange(parameters);
            return command;
        }

        public DbCommand CreateCommand(string commandText, CommandType type, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(commandText, parameters);
            command.CommandType = type;
            return command;
        }

        public DbCommand CreateCommand(string commandText, CommandType type, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(commandText, parameters);
            command.CommandType = type;
            return command;
        }

        public DbCommand CreateCommand(string commandText, CommandType type, DbTransaction transaction, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(commandText, type, parameters);
            command.Transaction = transaction;
            return command;
        }

        public DbCommand CreateCommand(string commandText, CommandType type, DbTransaction transaction, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(commandText, type, parameters);
            command.Transaction = transaction;
            return command;
        }

        public DbParameter CreateParameter(string name)
        {
            ConnectionStringSettings connectionString = getConnectionString();
            DbParameter parameter = null;
            DbProviderFactory factory = null;
            if (connectionString != null)
            {
                factory = DbProviderFactories.GetFactory(connectionString.ProviderName);
                parameter = factory.CreateParameter();
                parameter.ParameterName = name;
            }
            return parameter;
        }

        public DbParameter CreateParameter(string name, object value)
        {
            DbParameter parameter = CreateParameter(name);
            if (value == null || (value.GetType().Name == "DateTime" && Convert.ToDateTime(value) == DateTime.MinValue))
                parameter.Value = DBNull.Value;
            else
                parameter.Value = value;
            return parameter;
        }

        public DbParameter CreateParameter(string name, object value, DbType type)
        {
            DbParameter parameter = CreateParameter(name, value);
            if (parameter != null)
                parameter.DbType = type;
            return parameter;
        }

        public DbParameter CreateParameter(string name, object value, DbType type, ParameterDirection direction)
        {
            DbParameter parameter = CreateParameter(name, value, type);
            if (parameter != null)
                parameter.Direction = direction;
            return parameter;
        }

        public DbParameter CreateParameter(string name, object value, DbType type, ParameterDirection direction,int size)
        {
            DbParameter parameter = CreateParameter(name, value, type, direction);
            if (parameter != null)
                parameter.Size = size;
            return parameter;
        }

        public DbParameter CreateParameter(string name, DbType type, ParameterDirection direction)
        {
            DbParameter parameter = CreateParameter(name);
            if (parameter != null)
            {
                parameter.DbType = type;
                parameter.Direction = direction;
            }
            return parameter;
        }

        public DataTable Query(DbCommand command)
        {
            DataTable table = new DataTable();
            IDataReader reader = null;
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.Open();
                }
                reader = command.ExecuteReader();

                table.Load(reader);
              //add transaction
                // command.Connection.Close();                
                if (m_transaction == null)
                {
                    command.Connection.Close();
                }
                else if (m_transaction.Connection == null)
                {
                    command.Connection.Close();
                }
                //End
            }
            catch (Exception ex)
            {
                command.Connection.Close();
                throw ex;
            }
            return table;
        }

        public DataTable Query(string queryString, CommandType type, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(queryString, type, parameters);
            return Query(command);
        }

        public DataTable Query(string queryString, CommandType type, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(queryString, type, parameters);
            return Query(command);
        }

        public DataTable Query(string queryString, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(queryString, parameters);
            return Query(command);
        }

        public DataTable Query(string queryString, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(queryString, parameters);
            return Query(command);
        }

        public DataTable Query(string queryString)
        {
            DbCommand command = CreateCommand(queryString);
            return Query(command);
        }

        public int Execute(DbCommand command)
        {
            int result;
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.Open();
                }
                result = command.ExecuteNonQuery();             
                //add transaction
                // command.Connection.Close();                
                if (m_transaction == null)
                {
                    command.Connection.Close();
                }
                else if (m_transaction.Connection == null)
                {
                    command.Connection.Close();
                }
                //End
            }
            catch(Exception ex)
            {
                command.Connection.Close();
                throw ex;
            }
            return result;
        }

        public int Execute(string queryString, CommandType type, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(queryString, type, parameters);
            return Execute(command);
        }

        public int Execute(string queryString, CommandType type, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(queryString, type, parameters);
            return Execute(command);
        }

        public int Execute(string queryString, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(queryString, parameters);
            return Execute(command);
        }

        public int Execute(string queryString, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(queryString, parameters);
            return Execute(command);
        }

        public int Execute(string queryString)
        {
            DbCommand command = CreateCommand(queryString);
            return Execute(command);
        }

        public object ExecuteScalar(DbCommand command)
        {
            object result = null;
            try
            {
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.Open();
                }
                result = command.ExecuteScalar();
                //command.Connection.Close();
                //add transaction
                // command.Connection.Close();                
                if (m_transaction == null)
                {
                    command.Connection.Close();
                }
                else if (m_transaction.Connection == null)
                {
                    command.Connection.Close();
                }
                //End
            }
            catch (Exception ex)
            {
                command.Connection.Close();
                throw ex;
            }
            return result;
        }

        public object ExecuteScalar(string queryString, CommandType type, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(queryString, type, parameters);
            return ExecuteScalar(command);
        }

        public object ExecuteScalar(string queryString, CommandType type, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(queryString, type, parameters);
            return ExecuteScalar(command);
        }

        public object ExecuteScalar(string queryString, IList<DbParameter> parameters)
        {
            DbCommand command = CreateCommand(queryString, parameters);
            return ExecuteScalar(command);
        }

        public object ExecuteScalar(string queryString, params DbParameter[] parameters)
        {
            DbCommand command = CreateCommand(queryString, parameters);
            return ExecuteScalar(command);
        }

        public object ExecuteScalar(string queryString)
        {
            DbCommand command = CreateCommand(queryString);
            return ExecuteScalar(command);
        }
    }
}
